﻿using DataAccess.Enums;
using DataAccess.FrameWork;
using DbEntityTools.Dal;
using DbEntityTools.Model;
using DbEntityTools.Model.Entities;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;

namespace DbEntityTools.Service
{
    public class MySqlConnectionService
    {
        private string connectionStr = string.Empty;
        private MySqlConnection conn;
        public List<string> dbList = new List<string>();

        public MySqlConnectionService(string connStr)
        {
            connectionStr = connStr;
        }
        public bool TestConnection()
        {
            bool result = false;
            try
            {
                conn = new MySqlConnection(connectionStr);
                conn.Open();
                GetDatabases();
                result = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error connecting to the server: " + ex.Message);
            }
            return result;
        }

        private void GetDatabases()
        {
            MySqlDataReader reader = null;
            MySqlCommand cmd = new MySqlCommand("SHOW DATABASES", conn);
            try
            {
                reader = cmd.ExecuteReader();
                dbList.Clear();
                while (reader.Read())
                {
                    dbList.Add(reader.GetString(0));
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Failed to populate database list: " + ex.Message);
            }
            finally
            {
                if (reader != null) reader.Close();
            }
        }

        /// <summary>
        /// 获取table表
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public List<string> GetTables(string dbName)
        {
            var tables = new List<string>();
            MySqlDataReader reader = null;
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }
            conn.ChangeDatabase(dbName);
            MySqlCommand cmd = new MySqlCommand("SHOW TABLES", conn);
            try
            {
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    tables.Add(reader.GetString(0));
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Failed to populate table list: " + ex.Message);
            }
            finally
            {
                if (reader != null) reader.Close();
            }
            return tables;
        }

        public TableModel GetTableDetail(string dbName, string tableName)
        {
            TableModel result = new TableModel();
            try
            {
                var table = GetTable(dbName, tableName);
                result.TableName = table.TableName;
                result.DbName = table.DbName;
                result.TableDes = table.TableDes;
                var fields = GetTableField(dbName, tableName);
                result.SetFileds(fields);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return result;
        }

        private TableEntity GetTable(string dbName, string tableName)
        {
            TableEntity table = null;
            try
            {
                var param = new Dictionary<string, object>();
                param.Add("DbName", dbName);
                param.Add("TableName", tableName);
                var tables = DBHelper.SelectList<TableEntity>(new FrameWorkItem
                {
                    Sql = DataBaseDal.GetTableDetail(),
                    ConnectionString = connectionStr,
                    SqlParam = param,
                }, DBType.MYSQL, true);
                if (tables != null && tables.Count > 0)
                {
                    table = tables.First();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("查询表信息异常：" + ex.Message);
            }
            return table;
        }
        private List<FieldEntity> GetTableField(string dbName, string tableName)
        {

            var param = new Dictionary<string, object>();
            param.Add("DbName", dbName);
            param.Add("TableName", tableName);
            return DBHelper.SelectList<FieldEntity>(new FrameWorkItem
            {
                Sql = DataBaseDal.GetTableFieldsDetail(),
                ConnectionString = connectionStr,
                SqlParam = param,
            }, DBType.MYSQL, true);
        }
    }
}
